#导入专利族表和同族专利拆分表
library(readxl)
library(dplyr)
library(stringr)
library(tidyr)
library(tidytext)
library(ggplot2)
library(Cairo)
library(showtext)
library(openxlsx)
PFList <-read_excel("PFList.XLSX") 
AppList<-read_excel("APPList.XLSX")

#PFList<-PFList%>%rename('公开号'='公开(公告)号')

#================提取同族专利中有效专利及地域有效期，单件申请最大被引用数量，最早优先权日==============================

#从同族专利拆分表中查询匹配的专利申请
# returnLivePatentsInfo<-function(FamilyPatentsNumber){
#   AppList%>%
#     select(公开号,简单法律状态,预估到期日)%>%
#     filter(str_detect(FamilyPatentsNumber,公开号) & !str_detect(简单法律状态,"失效")& !str_detect(简单法律状态,"指定期满"))%>%
#     mutate(DomainandPeriod=paste(substr(公开号,0,2),预估到期日,sep="-"))
# }
# 
# CatchPNFromDf<-function(df){
#   paste(df$公开号,sep=" ",collapse=";")
# }
# CatchDomainandPeriodFromDf<-function(df){
#   paste(df$DomainandPeriod,sep=" ",collapse=";")
# }
# 
# temp<-PFList%>%select(公开号,INPADOC同族)%>%
#    mutate(LivePatentPN=lapply(INPADOC同族,function(x) CatchPNFromDf(returnLivePatentsInfo(x))),DomainandPeriod=lapply(INPADOC同族,function(x) CatchDomainandPeriodFromDf(returnLivePatentsInfo(x))))
# temp2<-temp%>%mutate(LivePatentPN=as.character(LivePatentPN),DomainandPeriod=as.character(DomainandPeriod))

#从同族专利列表INPADOC同族列拆分成多行，形成同族代表专利公开号PN与同族拆分专利公开号PF_unnest的对应表PFList_unnest。
PFList_unnest<-data.frame(PN=PFList$公开号,PF=PFList$PatSnap同族)%>%
  unnest_tokens(PF_unnest,PF,token=stringr::str_split,pattern="\\|")%>%
  mutate(PF_unnest=lapply(PF_unnest,str_to_upper))%>%
  mutate(PF_unnest=lapply(PF_unnest,str_trim,side="both"))%>%
  mutate(PF_unnest=as.character(PF_unnest))

#统计同族拆分专利每件申请的最早优先权日
AppMinPD<-data.frame(PN=AppList$公开号,AppDate=AppList$申请日,PriorityDate=AppList$优先权日)%>%
  mutate(PriorityDate=ifelse(PriorityDate=="-",AppDate,PriorityDate))%>%
  unnest_tokens(PD_unnest,PriorityDate,token=stringr::str_split,pattern="\\|")%>%
  mutate(PD_unnest=lapply(PD_unnest,str_trim,side="both"))%>%
  mutate(PD_unnest=as.character(PD_unnest))%>%
  group_by(PN)%>%
  summarise(MinPDbyApp=min(PD_unnest))

#将拆分同族代表专利公开号PN与同族拆分专利公开号PF_unnest的对应表PFList_unnest，与检索得到的按申请导出列表AppList，之间连接得到temp，带入按申请列表的全部字段，如被引用次数，法律状态等信息。
temp<-PFList_unnest%>%left_join(AppList,by=c("PF_unnest"="公开号"))%>%
  left_join(AppMinPD,by=c("PF_unnest"="PN"))%>%
  mutate(DomainandPeriod=paste(lapply(PF_unnest,str_sub,start=1,end=2),预估到期日))%>%
  mutate(CitedbyNum=as.numeric(被引用专利数量))

#连接后的temp表，按照同族专利代表专利公开号PN分组，求组内被引用次数最大值。
PFPN_MaxCitedNum<-temp%>%
  group_by(PN)%>%
  summarise(MaxCitedNum=max(CitedbyNum,na.rm=TRUE))
#连接后的temp表，按照同族专利代表专利公开号PN分组，并筛选法律状态非失效的专利，求组内处于有效期的专利号码集合以及国家和有效期集合。
LivePatentInfo<-temp%>%
  group_by(PN)%>%
  filter(!str_detect(简单法律状态,"失效")& !str_detect(简单法律状态,"指定期满"))%>%
  summarise(LivePatentList=paste(PF_unnest,sep=" ",collapse=";"),LiveomainandPeriod=paste(DomainandPeriod,sep=" ",collapse=";"))
#连接后的temp表，按照同族专利代表专利公开号分组，求族内申请最早优先权日
PFPN_MinPriorityDate<-temp%>%
  group_by(PN)%>%
  summarise(MinPriorityDatebyPF=min(MinPDbyApp,na.rm=TRUE))

#==结果导出
#写出txt文件
write.table(PFPN_MaxCitedNum,"PFPN_MaxCitedNum.txt",append=FALSE,sep = "\t",quote = FALSE,row.names = TRUE, col.names = TRUE,fileEncoding="UTF-8")
write.table(LivePatentInfo,"LivePatentInfo.txt",append=FALSE,sep = "\t",quote = FALSE,row.names = TRUE, col.names = TRUE,fileEncoding="UTF-8")
write.table(PFPN_MinPriorityDate,"PFPN_MinPriorityDate.txt",append=FALSE,sep = "\t",quote = FALSE,row.names = TRUE, col.names = TRUE,fileEncoding="UTF-8")
#写出excel文件

#v1 写出单独文件，需要后续复制和人工插入公式
#list_data <- list("PFPN_MaxCitedNum" = PFPN_MaxCitedNum, "LivePatentInfo" = LivePatentInfo, "PFPN_MinPriorityDate" = PFPN_MinPriorityDate)
#write.xlsx(list_data, file = "info.xlsx",overwrite = FALSE)

#v2 写出完整excel文件，带公式
wb <- loadWorkbook(file = "PFList.XLSX")
addWorksheet(wb,sheetName="PFPN_MaxCitedNum")
addWorksheet(wb,sheetName="PFPN_MinPriorityDate")
addWorksheet(wb,sheetName="LivePatentInfo")
writeData(wb,sheet='PFPN_MaxCitedNum',x=PFPN_MaxCitedNum)
writeData(wb,sheet='PFPN_MinPriorityDate',x=PFPN_MinPriorityDate)
writeData(wb,sheet='LivePatentInfo',x=LivePatentInfo)

df_formula<-data.frame(
  PFPN_MinPriorityDate = paste0("VLOOKUP(B",2:(nrow(PFList)+1L),",PFPN_MinPriorityDate!A:B,2,FALSE)"),
  PFPN_MaxCitedNum = paste0("VLOOKUP(B",2:(nrow(PFList)+1L),",PFPN_MaxCitedNum!A:B,2,FALSE)"),
  LivePatent = paste0("VLOOKUP(B",2:(nrow(PFList)+1L),",LivePatentInfo!A:C,2,FALSE)"),
  LivePatentDeadline = paste0("VLOOKUP(B",2:(nrow(PFList)+1L),",LivePatentInfo!A:C,3,FALSE)"),
  stringsAsFactors = FALSE
  )
class(df_formula$PFPN_MinPriorityDate) <- c(class(df_formula$PFPN_MinPriorityDate), "formula")
class(df_formula$PFPN_MaxCitedNum) <- c(class(df_formula$PFPN_MaxCitedNum), "formula")
class(df_formula$LivePatent) <- c(class(df_formula$LivePatent), "formula")
class(df_formula$LivePatentDeadline) <- c(class(df_formula$LivePatentDeadline), "formula")

#writeData(wb, sheet = "sheet1", x = "族内最大被引用次数",startCol = ncol(PFList)+1, startRow = 1)
writeData(wb, sheet = "sheet1", x = df_formula, startCol = ncol(PFList)+1, startRow = 1)

saveWorkbook(wb, "writeDataExample.xlsx", overwrite = TRUE)


#===================================统计各分类与最早优先权年之间的趋势============================================
#最早优先权年提取实现暂时在VBA环境中实现，并已通过Excel导入
#******上述代码已实现最早优先权日的计算，下面代码需要适应性修改
ClList_unnest<-data.frame(PN=PFList$公开号,MinPriorityYear=PFList$最早优先权年,Cl=PFList$技术分类)%>%
  unnest_tokens(Cl_unnest,Cl,token=stringr::str_split,pattern=";")%>%
  mutate(Cl_unnest=lapply(Cl_unnest,str_to_upper))%>%
  mutate(Cl_unnest=lapply(Cl_unnest,str_trim,side="both"))%>%
  mutate(Cl_unnest=as.character(Cl_unnest))%>%
  filter(!is.na(Cl_unnest) & Cl_unnest != "")%>%
  distinct(PN, Cl_unnest, .keep_all = TRUE)

#数据清理
ClList_unnest<-ClList_unnest%>%
  mutate(Cl_unnest=ifelse(str_detect(Cl_unnest,"快门眼镜"),"快门眼镜式",Cl_unnest))%>%
  mutate(Cl_unnest=ifelse(str_detect(Cl_unnest,"偏振"),"偏振眼镜式",Cl_unnest))%>%
  mutate(Cl_unnest=ifelse(str_detect(Cl_unnest,"人眼跟踪"),"人眼跟踪",Cl_unnest))%>%
  mutate(Cl_unnest=ifelse(str_detect(Cl_unnest,"视差屏障"),"视差屏障式",Cl_unnest))%>%
  mutate(Cl_unnest=ifelse(str_detect(Cl_unnest,"狭缝"),"视差屏障式",Cl_unnest))%>%
  mutate(Cl_unnest=ifelse(str_detect(Cl_unnest,"柱镜"),"柱状透镜式",Cl_unnest))%>%
  mutate(Cl_unnest=ifelse(str_detect(Cl_unnest,"3D拍摄"),"3D拍摄",Cl_unnest))


MultiClsList_unnest<-data.frame(PN=PFList$公开号,MinPriorityYear=PFList$最早优先权年,Cl1=PFList$技术分类,Cl2=PFList$技术问题)%>%
  unnest_tokens(Cl1_unnest,Cl1,token=stringr::str_split,pattern=";")%>%
  mutate(Cl1_unnest=lapply(Cl1_unnest,str_to_upper))%>%
  mutate(Cl1_unnest=lapply(Cl1_unnest,str_trim,side="both"))%>%
  mutate(Cl1_unnest=as.character(Cl1_unnest))%>%
  filter(!is.na(Cl1_unnest) & Cl1_unnest != "")%>%
  unnest_tokens(Cl2_unnest,Cl2,token=stringr::str_split,pattern=";")%>%
  mutate(Cl2_unnest=lapply(Cl2_unnest,str_to_upper))%>%
  mutate(Cl2_unnest=lapply(Cl2_unnest,str_trim,side="both"))%>%
  mutate(Cl2_unnest=as.character(Cl2_unnest))%>%
  filter(!is.na(Cl2_unnest) & Cl2_unnest != "")%>%
  distinct(PN, Cl1_unnest, Cl2_unnest,.keep_all = TRUE)%>%
  mutate(Cl2_unnest=ifelse(str_detect(Cl2_unnest,"快门眼镜"),"快门眼镜式",Cl2_unnest))%>%
  mutate(Cl2_unnest=ifelse(str_detect(Cl2_unnest,"偏振"),"偏振眼镜式",Cl2_unnest))%>%
  mutate(Cl2_unnest=ifelse(str_detect(Cl2_unnest,"人眼跟踪"),"人眼跟踪",Cl2_unnest))%>%
  mutate(Cl2_unnest=ifelse(str_detect(Cl2_unnest,"视差屏障"),"视差屏障式",Cl2_unnest))%>%
  mutate(Cl2_unnest=ifelse(str_detect(Cl2_unnest,"狭缝"),"视差屏障式",Cl2_unnest))%>%
  mutate(Cl2_unnest=ifelse(str_detect(Cl2_unnest,"柱镜"),"柱状透镜式",Cl2_unnest))%>%
  mutate(Cl2_unnest=ifelse(str_detect(Cl2_unnest,"3D拍摄"),"3D拍摄",Cl2_unnest))%>%
  group_by(Cl1_unnest,Cl2_unnest)%>%
  summarise(CountPF=n())

#统计分类和优先权年趋势
SummaryClandYear<-ClList_unnest%>%
  group_by(MinPriorityYear,Cl_unnest)%>%
  summarise(CountPF=n()) 
SummaryClandYearSpread<-SummaryClandYear%>%
  spread(Cl_unnest,CountPF)

# 图表绘制
#ggplot(data=SummaryClandYear,aes(x = MinPriorityYear,y=CountPF,fill=Cl_unnest))+geom_bar(stat="identity")
p1<-ggplot(data=SummaryClandYear,aes(x = MinPriorityYear,y=CountPF))+geom_bar(stat="identity")+facet_wrap(~Cl_unnest)+theme(strip.text.x = element_text(size = 20))
p2<-ggplot(MultiClsList_unnest,aes(x=Cl2_unnest,y=Cl1_unnest,color=Cl2_unnest))+geom_point(aes(size=CountPF),alpha=0.5)+scale_size_continuous(range = c(3,20)) +scale_fill_gradient(trans = 'log')
#用Cairo包输出
library(gridExtra)
CairoPDF("plotClandPriority.pdf",width = 30, height =20,family = "msyh")
showtext_begin()
grid.arrange(p1,nrow=1)
showtext_end()
dev.off()

CairoPDF("plotMultiClandPriority.pdf",width = 15, height =10,family = "msyh")
showtext_begin()
grid.arrange(p2,nrow=1)
showtext_end()
dev.off()

#结果导出
write.table(SummaryClandYear,"SummaryClandYear.txt",append=FALSE,sep = "\t",quote = FALSE,row.names = TRUE, col.names = TRUE,fileEncoding="UTF-8")
write.table(SummaryClandYearSpread,"SummaryClandYearSpread.txt",append=FALSE,sep = "\t",quote = FALSE,row.names = TRUE, col.names = TRUE,fileEncoding="UTF-8")